package com.gxuwz.lhleavesys.dao.impl;


import com.gxuwz.lhleavesys.dao.LeaveDao;
import com.gxuwz.lhleavesys.db.DbUtil;
import com.gxuwz.lhleavesys.entity.Leave;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

@Repository
public class LeaveDaoImpl implements LeaveDao {
    @Autowired
    private DbUtil dbUtil;


    /**
     * 根据id刪除請假單
     *
     * @param id
     * @return
     */
    @Override
    public boolean delete(String id) {
        String sql = "DELETE FROM sys_leave WHERE leaveID = ?";
        try(Connection conn = dbUtil.getConn();
            PreparedStatement ps = conn.prepareStatement(sql)){
            ps.setString(1, id);
            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        }catch (Exception e) {
        }
        return false;
    }

    /**
     * 修改请假单
     *
     * @param leave
     * @return
     */
    @Override
    public boolean update(Leave leave) {
        String sql = "UPDATE sys_leave SET courseID = ?,reason = ?,daynum = ?,styNo = ?,applytime = ?," +
                "status = ?,audittime = ?," +
                "opinion = ? WHERE leaveID = ?";
        try(Connection conn = dbUtil.getConn();
        PreparedStatement ps = conn.prepareStatement(sql)){
            ps.setString(1, leave.getCourseID());
            ps.setString(2, leave.getReason());
            ps.setInt(3, leave.getDaynum());
            ps.setString(4,leave.getStuNo());
            ps.setDate(5, (Date) leave.getApplytime());
            ps.setString(6,leave.getStatus());
            ps.setDate(7, (Date) leave.getAudittime());
            ps.setString(8,leave.getOpinion());
            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        }catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 新增请假单
     *
     * @param leave
     * @return
     */
    @Override
    public boolean insert(Leave leave) {
        String sql = "INSERT INTO sys_leave(leaveID,courseID,reason,daynum,stuNo,applytime,status) VALUES(?,?,?,?,?,?,?)";
        try(Connection conn = dbUtil.getConn();
        PreparedStatement ps = conn.prepareStatement(sql)){
            ps.setString(1,leave.getLeaveID());
            ps.setString(2,leave.getCourseID());
            ps.setString(3,leave.getReason());
            ps.setInt(4,leave.getDaynum());
            ps.setString(5,leave.getStuNo());
            ps.setDate(6, (Date) leave.getApplytime());
            ps.setString(7,leave.getStatus());
            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 根据请假单id查询请假单
     * @param LeaveID
     * @return
     */
    @Override
    public Leave findByLeaveID(String LeaveID) {
        Leave leave = null;
        String sql = "SELECT * FROM sys_leave WHERE leaveID = ?";
        try(Connection conn = dbUtil.getConn();
            PreparedStatement ps = conn.prepareStatement(sql)){
            ps.setString(1, LeaveID);
            try(ResultSet rs = ps.executeQuery()){
                if (rs.next()) {
                    leave = new Leave();
                    leave.setLeaveID(rs.getString("leaveID"));
                    leave.setCourseID(rs.getString("courseID"));
                    leave.setReason(rs.getString("reason"));
                    leave.setDaynum(rs.getInt("daynum"));
                    leave.setStuNo(rs.getString("stuNo"));
                    leave.setApplytime(rs.getDate("applytime"));
                    leave.setStatus(rs.getString("status"));
                    leave.setAudittime(rs.getDate("audittime"));
                    leave.setOpinion(rs.getString("opinion"));
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return leave;
    }

    /**
     * 查詢所有请假单
     * @return
     */
    @Override
    public List<Leave> findAll() {
        List<Leave> leaveList = new ArrayList<Leave>();
        String sql = "SELECT * FROM sys_leave";
        try(Connection conn = dbUtil.getConn();
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery()){
            while (rs.next()) {
                Leave leave = new Leave();
                leave.setLeaveID(rs.getString("leaveID"));
                leave.setCourseID(rs.getString("courseID"));
                leave.setReason(rs.getString("reason"));
                leave.setDaynum(rs.getInt("daynum"));
                leave.setStuNo(rs.getString("stuNo"));
                leave.setApplytime(rs.getDate("applytime"));
                leave.setStatus(rs.getString("status"));
                leave.setAudittime(rs.getDate("audittime"));
                leave.setOpinion(rs.getString("opinion"));
                leaveList.add(leave);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return leaveList;
    }

    @Override
    public Boolean examineLeave(String opinion, String id,String status,String formattedDate) {
        String sql = "UPDATE sys_leave SET opinion = ? , status = ? ,audittime = ? WHERE leaveID = ?";
        try(Connection conn = dbUtil.getConn();
            PreparedStatement ps = conn.prepareStatement(sql)){
            ps.setString(1, opinion);
            ps.setString(2, status);
            ps.setString(3, formattedDate);
            ps.setString(4, id);
            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        }catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public List<Leave> getLeaveByBlur(String leaveID, String courseID, String stuNo, String status) {
        List<Leave> leaveList = new ArrayList<>();

        // 初始 SQL 查询字符串
        StringBuilder sql = new StringBuilder("SELECT * FROM sys_leave WHERE 1=1");

        // 创建一个动态参数列表，用于存储需要添加到 SQL 查询的条件参数
        List<Object> params = new ArrayList<>();

        // 构建动态 SQL 查询
        if (leaveID != null && !leaveID.isEmpty()) {
            sql.append(" AND leaveID = ?");
            params.add(leaveID);
        }
        if (courseID != null && !courseID.isEmpty()) {
            sql.append(" AND courseID = ?");
            params.add(courseID);
        }
        if (stuNo != null && !stuNo.isEmpty()) {
            sql.append(" AND stuNo = ?");
            params.add(stuNo);
        }
        if (status != null && !status.isEmpty()) {
            sql.append(" AND status = ?");
            params.add(status);
        }

        try (Connection conn = dbUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql.toString())) {
            // 设置动态参数值
            for (int i = 0; i < params.size(); i++) {
                ps.setObject(i + 1, params.get(i));
            }

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Leave leave = new Leave();
                    leave.setLeaveID(rs.getString("leaveID"));
                    leave.setCourseID(rs.getString("courseID"));
                    leave.setReason(rs.getString("reason"));
                    leave.setDaynum(rs.getInt("daynum"));
                    leave.setStuNo(rs.getString("stuNo"));
                    leave.setApplytime(rs.getDate("applytime"));
                    leave.setStatus(rs.getString("status"));
                    leave.setAudittime(rs.getDate("audittime"));
                    leave.setOpinion(rs.getString("opinion"));
                    leaveList.add(leave);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return leaveList;
    }

    @Override
    public List<Leave> findLeaveBystuNo(String stuName) {
        List<Leave> leaveList = new ArrayList<Leave>();
        String sql = "SELECT * FROM sys_leave WHERE stuNo = ?";
        try (Connection conn = dbUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            // Set the stuName parameter
            ps.setString(1, stuName);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Leave leave = new Leave();
                    leave.setLeaveID(rs.getString("leaveID"));
                    leave.setCourseID(rs.getString("courseID"));
                    leave.setReason(rs.getString("reason"));
                    leave.setDaynum(rs.getInt("daynum"));
                    leave.setStuNo(rs.getString("stuNo"));
                    leave.setApplytime(rs.getDate("applytime"));
                    leave.setStatus(rs.getString("status"));
                    leave.setAudittime(rs.getDate("audittime"));
                    leave.setOpinion(rs.getString("opinion"));
                    leaveList.add(leave);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return leaveList;
    }



}
